USE MaryaDB
GO

SELECT t4.Name AS Region, t2.Name AS Store, M.Name, t5.Name AS DayType,
	ISNULL(DayReport.Sum,0) AS DaySum, ISNULL(DayReport.Count,0) AS DayCount, ISNULL(DP,0) AS DayPayments,
	ISNULL(MonthReport.Sum,0) AS MonthSum, ISNULL(MonthReport.Count,0) AS MonthCount, ISNULL(MP,0) AS MonthPayments,
	ISNULL(LastMonthReport.Sum,0) AS LastMonthSum, ISNULL(LastMonthReport.Count,0) AS LastMonthCount, ISNULL(LMP,0) AS LastMonthPayments   
FROM Manager AS M

LEFT JOIN (SELECT M.ManagerID AS ID, COUNT(*) AS Count, SUM(Cost) AS Sum 
	FROM Manager AS M
	LEFT JOIN Contract AS C
	ON M.ManagerID = C.ManagerID
	WHERE C.Date = '2012-06-24'
	GROUP BY M.ManagerID) AS DayReport
ON M.ManagerID = DayReport.ID

LEFT JOIN (SELECT M.ManagerID, SUM(Amount) AS DP
	FROM Manager AS M
	INNER JOIN Contract AS C
	ON M.ManagerID = C.ManagerID
	LEFT JOIN Payment AS P
	ON C.ContractID = P.ContractID
	WHERE P.Date = '2012-06-24' 
	GROUP BY M.ManagerID) AS DayPayments
ON M.ManagerID = DayPayments.ManagerID

LEFT JOIN (SELECT M.ManagerID AS ID, COUNT(*) AS Count, SUM(Cost) AS Sum
	FROM Manager AS M
	LEFT JOIN Contract AS C
	ON M.ManagerID = C.ManagerID
	WHERE C.Date BETWEEN '2012-06-01' 
	AND '2012-06-24'
	GROUP BY M.ManagerID) AS MonthReport
ON M.ManagerID = MonthReport.ID

LEFT JOIN (SELECT M.ManagerID, SUM(Amount) AS MP
	FROM Manager AS M
	INNER JOIN Contract AS C
	ON M.ManagerID = C.ManagerID
	LEFT JOIN Payment AS P
	ON C.ContractID = P.ContractID
	WHERE P.Date BETWEEN '2012-06-01' 
	AND '2012-06-24' 
	GROUP BY M.ManagerID) AS MonthPayments
ON M.ManagerID = MonthPayments.ManagerID

LEFT JOIN (SELECT M.ManagerID AS ID, COUNT(*) AS Count, SUM(Cost) AS Sum    
	FROM Manager AS M
	LEFT JOIN Contract AS C
	ON M.ManagerID = C.ManagerID
	WHERE C.Date BETWEEN '2012-05-01' 
	AND '2012-05-31'
	GROUP BY M.ManagerID) AS LastMonthReport
ON M.ManagerID = LastMonthReport.ID

LEFT JOIN (SELECT M.ManagerID, SUM(Amount) AS LMP
	FROM Manager AS M
	INNER JOIN Contract AS C
	ON M.ManagerID = C.ManagerID
	LEFT JOIN Payment AS P
	ON C.ContractID = P.ContractID
	WHERE P.Date BETWEEN '2012-05-01' 
	AND '2012-05-31' 
	GROUP BY M.ManagerID) AS LastMonthPayments
ON M.ManagerID = LastMonthPayments.ManagerID

INNER JOIN Tree AS t1
ON M.ManagerID = t1.id

INNER JOIN Tree AS t2
ON t1.pid = t2.id

INNER JOIN Tree AS t3
ON t2.pid = t3.id

INNER JOIN Tree AS t4
ON t3.pid = t4.id

INNER JOIN Calendar AS C
ON M.ManagerID = C.ManagerID

INNER JOIN Tree AS t5
ON C.DayTypeID = t5.id

WHERE C.Date = '2012-06-24'
ORDER BY M.ManagerID